﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Lines
{
    internal static class FeatureDAL
    {
        internal static FeatureEntity GetFeatureByReader(IDataReader dr)
        {
            FeatureEntity feature = new FeatureEntity();

            feature.LineID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            feature.ShopID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            feature.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
            feature.DestinationProvinceID = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            feature.DestinationCityID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            feature.DestinationAreaID = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            feature.StagingProvinceID = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            feature.StagingCityID = dr.IsDBNull(7) ? 0 : dr.GetInt32(7);
            feature.StagingAreaID = dr.IsDBNull(8) ? 0 : dr.GetInt32(8);
            feature.Type = dr.IsDBNull(9) ? 0 : dr.GetInt32(9);
            feature.Days = dr.IsDBNull(10) ? 0 : dr.GetInt32(10);
            feature.Holiday = dr.IsDBNull(11) ? 0 : dr.GetInt32(11);
            feature.Stay = dr.IsDBNull(12) ? 0 : dr.GetInt32(12);
            feature.Grade = dr.IsDBNull(13) ? 0 : dr.GetInt32(13);
            feature.Price = dr.IsDBNull(14) ? 0 : dr.GetDecimal(14);
            feature.LineFeatures = dr.IsDBNull(15) ? null : dr.GetString(15);
            feature.ReferenceStroke = dr.IsDBNull(16) ? null : dr.GetString(16);
            feature.CostDescription = dr.IsDBNull(17) ? null : dr.GetString(17);
            feature.Precautions = dr.IsDBNull(18) ? null : dr.GetString(18);
            feature.Hits = dr.IsDBNull(19) ? 0 : dr.GetInt32(19);
            feature.Created = Util.ConverDateTime(dr.GetValue(20));
            feature.IsEnable = dr.IsDBNull(21) ? false : dr.GetBoolean(21);
            feature.ShopName = dr.IsDBNull(22) ? null : dr.GetString(22);
            feature.FeatureID = dr.IsDBNull(23) ? 0 : dr.GetInt32(23);
            feature.Sort = dr.IsDBNull(24) ? 0 : dr.GetInt32(24);
            feature.FeatureCreated = Util.ConverDateTime(dr.GetValue(25));

            return feature;
        }

        internal static int GetFeatureCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectFeature = @"SELECT COUNT([LineFeature].[FeatureID]) FROM [LineFeature] 
                INNER JOIN Line ON LineFeature.LineID = Line.LineID 
                LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectFeature, ref parameterlst);
            DbCommand SelectFeatureCommand = db.GetSqlStringCommand(SelectFeature);
            DbHelper.PackDbCommandByDbParameters(SelectFeatureCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectFeatureCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<FeatureEntity> GetFeatureList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<FeatureEntity> featurelst = new List<FeatureEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectFeature = @"
                    SELECT [LineID],[ShopID],[Name],[DestinationProvinceID],[DestinationCityID],[DestinationAreaID],
                        [StagingProvinceID],[StagingCityID],[StagingAreaID],[Type],[Days],[Holiday],[Stay],[Grade],[Price],
                        [LineFeatures],[ReferenceStroke],[CostDescription],[Precautions],[Hits],[Created],[IsEnable],
                        [ShopName],[FeatureID],[Sort],[FeatureCreated],[DesProvinceName],[StaProvinceArea],dbo.F_GetPhotoUrl([LineID],1,0) AS ImgUrl01,
                        dbo.F_GetDepartureDate(LineID,0) AS DepartureDate
                    FROM (
                        SELECT Line.LineID, Line.ShopID, Line.Name, Line.DestinationProvinceID, Line.DestinationCityID, 
                            Line.DestinationAreaID, Line.StagingProvinceID, Line.StagingCityID, Line.StagingAreaID, 
                            Line.Type, Line.Days, Line.Holiday, Line.Stay, Line.Grade, Line.Price, Line.LineFeatures, 
                            Line.ReferenceStroke, Line.CostDescription, Line.Precautions, Line.Hits, Line.Created, 
                            Line.IsEnable, Shop.Name AS ShopName, LineFeature.FeatureID, LineFeature.Sort, LineFeature.Created AS FeatureCreated,
                            DesProvinceArea.Name AS DesProvinceName, StaProvinceArea.Name AS StaProvinceArea,
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectFeature += " " + sortField + " " + sortMode + ",";
            SelectFeature += @" [LineFeature].[FeatureID] Desc
                    ) AS rowNum 
                        FROM [LineFeature] 
                            INNER JOIN Line ON LineFeature.LineID = Line.LineID 
                            LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                            LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                            LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectFeature, ref parameterlst);
            SelectFeature += @"
                    ) AS [LineFeature] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectFeatureCommand = db.GetSqlStringCommand(SelectFeature);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectFeatureCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectFeatureCommand))
            {
                while (dr.Read())
                {
                    FeatureEntity feature = GetFeatureByReader(dr);
                    feature.DesProvinceName = (dr["DesProvinceName"] == DBNull.Value ? null : dr["DesProvinceName"].ToString());
                    feature.StaProvinceArea = (dr["StaProvinceArea"] == DBNull.Value ? null : dr["StaProvinceArea"].ToString());
                    feature.ImgUrl01 = string.IsNullOrEmpty(dr["ImgUrl01"].ToString()) ? "Null.jpg" : dr["ImgUrl01"].ToString();
                    feature.DepartureDate = Util.ConverDateTime(dr["DepartureDate"]);
                    featurelst.Add(feature);
                }
            }

            return featurelst;
        }

        internal static List<FeatureEntity> GetFeatureList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<FeatureEntity> featurelst = new List<FeatureEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectFeature = @"
                    SELECT Line.LineID, Line.ShopID, Line.Name, Line.DestinationProvinceID, Line.DestinationCityID, 
                            Line.DestinationAreaID, Line.StagingProvinceID, Line.StagingCityID, Line.StagingAreaID, 
                            Line.Type, Line.Days, Line.Holiday, Line.Stay, Line.Grade, Line.Price, Line.LineFeatures, 
                            Line.ReferenceStroke, Line.CostDescription, Line.Precautions, Line.Hits, Line.Created, 
                            Line.IsEnable, Shop.Name AS ShopName, LineFeature.FeatureID, LineFeature.Sort, LineFeature.Created AS FeatureCreated,
                            DesProvinceArea.Name AS DesProvinceName, StaProvinceArea.Name AS StaProvinceArea,
                    FROM [LineFeature] 
                        INNER JOIN Line ON LineFeature.LineID = Line.LineID 
                        LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                        LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                        LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectFeature, ref parameterlst);
            SelectFeature += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectFeature += " " + sortField + " " + sortMode + ",";
            SelectFeature += " [LineFeature].[FeatureID] Desc ";

            DbCommand SelectFeatureCommand = db.GetSqlStringCommand(SelectFeature);
            DbHelper.PackDbCommandByDbParameters(SelectFeatureCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectFeatureCommand))
            {
                while (dr.Read())
                {
                    FeatureEntity feature = GetFeatureByReader(dr);
                    feature.DesProvinceName = (dr["DesProvinceName"] == DBNull.Value ? null : dr["DesProvinceName"].ToString());
                    feature.StaProvinceArea = (dr["StaProvinceArea"] == DBNull.Value ? null : dr["StaProvinceArea"].ToString());
                    featurelst.Add(feature);
                }
            }

            return featurelst;
        }

        internal static FeatureEntity GetFeature(int featureID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            FeatureEntity feature = null;

            string SelectFeature = @"
                    SELECT Line.LineID, Line.ShopID, Line.Name, Line.DestinationProvinceID, Line.DestinationCityID, 
                            Line.DestinationAreaID, Line.StagingProvinceID, Line.StagingCityID, Line.StagingAreaID, 
                            Line.Type, Line.Days, Line.Holiday, Line.Stay, Line.Grade, Line.Price, Line.LineFeatures, 
                            Line.ReferenceStroke, Line.CostDescription, Line.Precautions, Line.Hits, Line.Created, 
                            Line.IsEnable, Shop.Name AS ShopName, LineFeature.FeatureID, LineFeature.Sort, LineFeature.Created AS FeatureCreated,
                            DesProvinceArea.Name AS DesProvinceName, StaProvinceArea.Name AS StaProvinceArea
                    FROM [LineFeature] 
                        INNER JOIN Line ON LineFeature.LineID = Line.LineID 
                        LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                        LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                        LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID
                    WHERE [LineFeature].[FeatureID]=@FeatureID
                ";

            DbCommand SelectFeatureCommand = db.GetSqlStringCommand(SelectFeature);
            db.AddInParameter(SelectFeatureCommand, "@FeatureID", DbType.Int32, featureID);

            using (IDataReader dr = db.ExecuteReader(SelectFeatureCommand))
            {
                if (dr.Read())
                {
                    feature = GetFeatureByReader(dr);
                    feature.DesProvinceName = (dr["DesProvinceName"] == DBNull.Value ? null : dr["DesProvinceName"].ToString());
                    feature.StaProvinceArea = (dr["StaProvinceArea"] == DBNull.Value ? null : dr["StaProvinceArea"].ToString());
                }
            }

            return feature;
        }

        internal static int InsertFeature(FeatureEntity feature)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertFeature = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertFeature.Append(@"Declare @FeatureID int;
                    INSERT INTO [LineFeature] 
				([LineID],[Sort],[ProvinceID],[CityID],[Created])
				 VALUES (@LineID,@Sort,@ProvinceID,@CityID,@Created);
                    SET @FeatureID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@LineID", DbType.Int32, feature.LineID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, feature.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, feature.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, feature.CityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, feature.Created));


            InsertFeature.Append("SELECT @FeatureID;");

            DbCommand InsertFeatureCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertFeature", InsertFeature.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertFeatureCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertFeatureCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateFeature(FeatureEntity feature)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateFeature = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateFeature.Append(@"UPDATE [LineFeature] 
				 SET [LineID]=@LineID,[Sort]=@Sort,[ProvinceID]=@ProvinceID,[CityID]=@CityID,[Created]=@Created 
				 WHERE [FeatureID] = @FeatureID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@FeatureID", DbType.Int32, feature.FeatureID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@LineID", DbType.Int32, feature.LineID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, feature.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, feature.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, feature.CityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, feature.Created));


            DbCommand UpdateFeatureCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateFeature", UpdateFeature.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateFeatureCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateFeatureCommand);

            return result;
        }

        internal static int DeleteFeature(int featureID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteFeature = @"
                DELETE FROM [LineFeature] WHERE FeatureID=@FeatureID";

            DbCommand DeleteFeatureCommand = db.GetSqlStringCommand(DeleteFeature);
            db.AddInParameter(DeleteFeatureCommand, "@FeatureID", DbType.Int32, featureID);
            result = db.ExecuteNonQuery(DeleteFeatureCommand);

            return result;
        }

        internal static int SetFeatureEnable(int featureID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetFeature = @"UPDATE [LineFeature]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [FeatureID] = @FeatureID";

            DbCommand SetFeatureCommand = db.GetSqlStringCommand(SetFeature);
            db.AddInParameter(SetFeatureCommand, "@FeatureID", DbType.Int32, featureID);
            result = db.ExecuteNonQuery(SetFeatureCommand);

            return result;
        }

        internal static int HitFeature(int featureID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetFeature = @"UPDATE [LineFeature]
                    SET [Hits] = [Hits]+1
                    WHERE [FeatureID] = @FeatureID";

            DbCommand SetFeatureCommand = db.GetSqlStringCommand(SetFeature);
            db.AddInParameter(SetFeatureCommand, "@FeatureID", DbType.Int32, featureID);
            result = db.ExecuteNonQuery(SetFeatureCommand);

            return result;
        }
    }
}
